package com.judian.smart.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.blankj.utilcode.util.LogUtils;
import com.judian.smart.R;
import com.judian.smart.activity.MainActivity;
import com.judian.smart.base.MyApplication;
import com.judian.smart.constant.Constants;
import com.judian.smart.interfaces.IWidgetDAO;
import com.judian.smart.model.Device;
import com.judian.smart.model.DeviceType;
import com.judian.smart.model.Floor;
import com.judian.smart.model.GateWay;
import com.judian.smart.model.GatewayType;
import com.judian.smart.model.Mode;
import com.judian.smart.model.ModeList;
import com.judian.smart.model.ProCase;
import com.judian.smart.model.Products;
import com.judian.smart.model.RedInfra;
import com.judian.smart.model.Room;
import com.judian.smart.model.VoiceInfo;
import com.judian.smart.util.ProCaseUtils;
import com.judian.smart.util.UpdataAndDownload;
import com.judian.smart.view.DialogAlert;
import com.tencent.bugly.crashreport.CrashReport;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

/**
 * author: Peter
 * date: 2019/08/09 星期五 11:30
 * desc:
 */
public class WidgetDAOImpl implements IWidgetDAO {
    public static final String TABLE_ROOM = "t_room";
    public static final String TABLE_MODE = "t_mode";
    public static final String TABLE_USER = "t_user";
    public static final String TABLE_MODE_LIST = "t_mode_list";
    public static final String TABLE_DEVICE = "t_device";
    public static final String TABLE_DEVICE_ACTION = "t_device_action";
    public static final String TABLE_DEVICE_FUNCTION = "t_device_function";
    public static final String TABLE_FUNCTION_PARAM = "t_function_param";

    public static final String TABLE_ACTION_PARAM = "t_action_param";
    public static final String TABLE_DEVICE_TYPE = "t_device_type";
    public static final String TABLE_PRODUCTS = "t_products";

    public static final String TABLE_GATEWAY = "t_gateway";

    public static final String TABLE_GATEWAY_TYPE = "t_gateway_type";
    public static final String TABLE_INFRARED = "t_infrared";
    public static final String TABLE_FLOOR = "t_floor";
    public static final String TABLE_VOICE = "t_voice";

    private SQLiteDatabase db;

    public WidgetDAOImpl(SQLiteDatabase db) {
        //Ling 2019.7.8 打开数据库时判断数据库是否损坏，若损坏则删除然后将基础数据库复制过来并打开，不然正常打开
        if (UpdataAndDownload.isHaveTable(db)) {
            this.db = db;
        } else {
            ProCase proCase = ProCaseUtils.getInstance().getCurrentProCase();
            //数据库路径
            String path = MyApplication.getInstance().getRootPath()
                    + File.separator + proCase.getProCaseAlia() + File.separator + "homedatabases.db";
            if (db != null && db.isOpen()) {
                db.close();
            }
            //删除数据库
            File file = new File(path);
            file.delete();

            //将新的数据库加入原方案文件
            FileOutputStream fos;
            InputStream is;
            try {
                is = MyApplication.getInstance().getResources()
                        .openRawResource(R.raw.homedatabases);
                fos = new FileOutputStream(path);
                byte[] buffer = new byte[8192];
                int count;
                while ((count = is.read(buffer)) > 0) {
                    fos.write(buffer, 0, count);
                    fos.flush();
                }
            } catch (Exception e) {
                System.out.println(e.toString());
            }
            this.db = SQLiteDatabase.openOrCreateDatabase(path, null);
            final DialogAlert alert = new DialogAlert(MainActivity.getInstance());
            alert.init("温馨提示", "数据库异常，当前家庭数据已重置，请重新下载专案");
            alert.setSureListener(new DialogAlert.OnSureListener() {
                @Override
                public void onCancel() {

                }

                @Override
                public void onSure() {

                }
            });
            alert.show();
        }
    }


    public List<GateWay> getGateWay() {
        List<GateWay> list = new ArrayList<GateWay>();
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_GATEWAY, new String[]{"Id",
                                "GatewayName", "GatewayID", "UserName", "UserPassWord",
                                "TypeId", "IsCurrent", "State", "data1", "data2"}, null, null, null,
                        null, "Seqencing asc");
                //Ling 2019.8.19 网关排序由 typeId决定改为由Seqencing决定
                int resultCount = cursor.getCount();
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    GateWay gateWay = new GateWay();
                    gateWay.setId(cursor.getInt(0));
                    gateWay.setGatewayName(cursor.getString(1));
                    gateWay.setGatewayID(cursor.getString(2));
                    gateWay.setUserName(cursor.getString(3));
                    gateWay.setUserPassWord(cursor.getString(4));
                    gateWay.setTypeId(cursor.getInt(5));
                    gateWay.setIsCurrent(cursor.getInt(6));
                    gateWay.setGateWayPoint(cursor.getString(7));
                    gateWay.setData1(cursor.getString(8));
                    gateWay.setData2(cursor.getString(9));
                    list.add(gateWay);
                    cursor.moveToNext();
                }
                cursor.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public List<GatewayType> getGateWayType() {
        List<GatewayType> list = new ArrayList<GatewayType>();
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_GATEWAY_TYPE, new String[]{"Id",
                                "GatewayTypeName", "GatewayTypeImage", "CreationTime", "TypeId"}, null,
                        null, null, null, "TypeId asc");
                int resultCount = cursor.getCount();
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    GatewayType gateWayType = new GatewayType();
                    gateWayType.setId(cursor.getInt(0));
                    gateWayType.setGatewayTypeName(cursor.getString(1));
                    gateWayType.setGatewayTypeImage(cursor.getString(2));
                    gateWayType.setTypeId(cursor.getInt(4));
                    list.add(gateWayType);
                    cursor.moveToNext();
                }
                cursor.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public List<Mode> getMode() {
        List<Mode> list = new ArrayList<Mode>();
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_MODE, new String[]{"Id", "ModeName",
                                "ModeImage", "ModeType", "ModeCode", "ModeLoop",
                                "StartTime", "EndTime", "ModeNickName", "IsShow", "Seqencing", "GatewayID", "floorName", "floorId", "roomId", "roomName"}, null, null, null, null,
                        "Seqencing asc");
                int resultCount = cursor.getCount();
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    Mode mode = new Mode();
                    mode.setId(cursor.getInt(0));
                    mode.setModeName(cursor.getString(cursor
                            .getColumnIndex("ModeName")));
                    String modeImage = cursor
                            .getString(cursor.getColumnIndex("ModeImage"));
                    if (modeImage == null || modeImage == "" || modeImage.equals("")) {
                        mode.setModeImage(Constants.MODEIMAGEPATH);
                    } else {
                        mode.setModeImage(modeImage);
                    }
                    mode.setModeType(cursor.getString(cursor
                            .getColumnIndex("ModeType")));
                    mode.setModeCode(cursor.getInt(cursor
                            .getColumnIndex("ModeCode")));
                    mode.setSeqencing(cursor.getInt(cursor
                            .getColumnIndex("Seqencing")));
                    mode.setModeLoop(cursor.getInt(cursor
                            .getColumnIndex("ModeLoop")));
                    mode.setStartTime(cursor.getString(cursor
                            .getColumnIndex("StartTime")));
                    mode.setEndTime(cursor.getString(cursor
                            .getColumnIndex("EndTime")));
                    mode.setModeNickName(cursor.getString(cursor
                            .getColumnIndex("ModeNickName")));
                    mode.setCreationTime("");
                    mode.setGatewayID(cursor.getString(cursor
                            .getColumnIndex("GatewayID")));
                    mode.setIsShow(cursor.getInt(cursor
                            .getColumnIndex("IsShow")));
                    mode.setFloorName(cursor.getString(cursor
                            .getColumnIndex("floorName")));
                    mode.setFloorId(cursor.getInt(cursor
                            .getColumnIndex("floorId")));
                    mode.setRoomId(cursor.getInt(cursor
                            .getColumnIndex("roomId")));
                    mode.setRoomName(cursor.getString(cursor
                            .getColumnIndex("roomName")));
                    list.add(mode);
                    cursor.moveToNext();
                }
                cursor.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public List<ModeList> getModeList() {
        List<ModeList> list = new ArrayList<ModeList>();
        try {
            String sql = "select *,l.Id as m_id,l.CreationTime as mouth,d.DeviceImage,DT.DeviceTypeName,DT.DeviceTypeKey,r.RoomName from t_mode_list l left join t_device d on d.Id=l.DeviceId left join t_room r on r.Id=d.RoomId left join t_products p on d.ProductsKey=p.ProductsKey LEFT JOIN t_device_type dt on d.DeviceTypeKey=dt.DeviceTypeKey where d.Id=l.DeviceId order by l.Seqencing";
            if (db != null) {
                Cursor cursor = db.rawQuery(sql, null);
                if (cursor != null) {
                    int resultCount = cursor.getCount();

                    if (resultCount == 0 || !cursor.moveToFirst()) {
                        return list;
                    }
                    for (int i = 0; i < resultCount; i++) {
                        ModeList modeList = new ModeList();
                        modeList.setId(cursor.getInt(cursor.getColumnIndex("m_id")));

                        String ModeAction = cursor.getString(cursor
                                .getColumnIndex("ModeAction"));
                        String ModeFunction = cursor.getString(cursor
                                .getColumnIndex("ModeFunction"));
                        String ModeTime = cursor.getString(cursor
                                .getColumnIndex("ModeTime"));
                        String ModeDelayed = cursor.getString(cursor
                                .getColumnIndex("ModeDelayed"));
                        String ModePeriod = cursor.getString(cursor
                                .getColumnIndex("ModePeriod"));
                        modeList.setModeAction(ModeAction);
                        modeList.setModeFunction(ModeFunction);
                        modeList.setModeTime(ModeTime);
                        modeList.setModeDelayed(ModeDelayed);
                        modeList.setModePeriod(ModePeriod);

                        modeList.setModeId(cursor.getInt(cursor
                                .getColumnIndex("ModeId")));
                        modeList.setDeviceId(cursor.getInt(cursor
                                .getColumnIndex("DeviceId")));

                        modeList.setDeviceNickName(cursor.getString(cursor
                                .getColumnIndex("DeviceNickName")));
                        modeList.setRoomName(cursor.getString(cursor
                                .getColumnIndex("RoomName")));
                        modeList.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        modeList.setDeviceTypeClick(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeClick")));
                        modeList.setSeqencing(cursor.getInt(cursor
                                .getColumnIndex("Seqencing")));
                        modeList.setDeviceOnLine(cursor.getInt(cursor
                                .getColumnIndex("DeviceOnLine")));
                        modeList.setDeviceTimer(cursor.getString(cursor
                                .getColumnIndex("DeviceTimer")));
                        modeList.setDeviceOrdered(cursor.getString(cursor
                                .getColumnIndex("DeviceOrdered")));
                        modeList.setDeviceName(cursor.getString(cursor
                                .getColumnIndex("DeviceName")));
                        modeList.setDeviceID(cursor.getString(cursor
                                .getColumnIndex("DeviceID")));

                        String deviceImage = cursor.getString(cursor
                                .getColumnIndex("DeviceImage"));
                        modeList.setDeviceImage(deviceImage);
                        modeList.setDeviceIO(cursor.getString(cursor
                                .getColumnIndex("DeviceIO")));
                        modeList.setDeviceTypeName(cursor.getString(cursor
                                .getColumnIndex("DeviceTypeName")));
                        modeList.setProductsCode(cursor.getString(cursor
                                .getColumnIndex("ProductsCode")));
                        modeList.setStartTime(cursor.getString(cursor
                                .getColumnIndex("StartTime")));
                        modeList.setEndTime(cursor.getString(cursor
                                .getColumnIndex("EndTime")));
                        modeList.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        modeList.setProductsKey(cursor.getInt(cursor
                                .getColumnIndex("ProductsKey")));
                        modeList.setProductsIO(cursor.getInt(cursor
                                .getColumnIndex("ProductsIO")));
                        modeList.setBeginMonth(cursor.getString(cursor
                                .getColumnIndex("BeginMonth")));
                        modeList.setEndMonth(cursor.getString(cursor
                                .getColumnIndex("EndMonth")));
                        modeList.setModeWeek(cursor.getInt(cursor
                                .getColumnIndex("ModeWeek")));
                        modeList.setModeDay(cursor.getInt(cursor
                                .getColumnIndex("ModeDay")));
                        list.add(modeList);
                        cursor.moveToNext();
                    }
                    cursor.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Products> getProducts() {
        List<Products> list = new ArrayList<>();
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_PRODUCTS, new String[]{"Id",
                        "ProductsName", "ProductsImage", "ProductsCode", "ProductsKey", "ProductsIO", "DeviceTypeKey", "ActionShow", "FunctionShow", "ActionPramShow",
                        "FunctionParamShow"}, null, null, null, null, null);
                int resultCount = cursor.getCount();
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    Products products = new Products();
                    products.setId(cursor.getInt(0));
                    products.setProductsName(cursor.getString(cursor
                            .getColumnIndex("ProductsName")));
                    products.setProductsCode(cursor.getString(cursor
                            .getColumnIndex("ProductsCode")));
                    products.setProductsIO(cursor.getInt(cursor
                            .getColumnIndex("ProductsIO")));
                    products.setProductsKey(cursor.getInt(cursor
                            .getColumnIndex("ProductsKey")));
                    products.setDeviceTypekey(cursor.getInt(cursor
                            .getColumnIndex("DeviceTypeKey")));
                    products.setProductsImage(cursor.getString(cursor
                            .getColumnIndex("ProductsImage")));
                    products.setActionShow(cursor.getInt(cursor
                            .getColumnIndex("ActionShow")));
                    products.setFunctionShow(cursor.getInt(cursor
                            .getColumnIndex("FunctionShow")));
                    products.setActionPramShow(cursor.getInt(cursor
                            .getColumnIndex("ActionPramShow")));
                    products.setFunctionParamShow(cursor.getInt(cursor
                            .getColumnIndex("FunctionParamShow")));
                    list.add(products);
                    cursor.moveToNext();
                }
                cursor.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public void deleteAllRMS() {
        db.delete("t_button", "BtnType=?", new String[]{String.valueOf(0)});
    }


    @Override
    public List<Floor> getFloor() {
        List<Floor> list = new ArrayList<Floor>();

//        private int Id;
//        private String floorName;
//        private String floorImage;
//        private int floorId;
//        private String creationTime;
//        private String languageId;
//        private int Seqencing;
//        private int isShow;
//        private String actar;
//        private String graffitIndex;

        if (db != null) {
            Cursor cursor = db.query(TABLE_FLOOR, new String[]{"Id", "floorName",
                            "floorImage", "floorId", "CreationTime", "LanguageId", "Seqencing", "isShow", "avtar", "graffitIndex", "nikeName"},
                    null, null, null, null, "Seqencing asc");
            int resultCount = cursor.getCount();
            try {
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    Floor floor = new Floor();
                    floor.setId(cursor.getInt(0));
                    floor.setFloorName(cursor.getString(1));
                    floor.setFloorImage(cursor.getString(2));
                    floor.setFloorId(cursor.getInt(3));
                    floor.setCreationTime(cursor.getString(4));
                    floor.setLanguageId(cursor.getString(5));
                    floor.setSeqencing(cursor.getInt(6));
                    floor.setIsShow(cursor.getInt(7));
                    floor.setAvtar(cursor.getString(8));
                    floor.setGraffitIndex(cursor.getString(9));
                    floor.setNikeName(cursor.getString(10));
                    list.add(floor);
                    cursor.moveToNext();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                cursor.close();
            }
        }
        return list;
    }

    public int deleteRMCBean(int id) {
        int resultCount;
        try {
            resultCount = db.delete("t_button", "Id=?",
                    new String[]{String.valueOf(id)});

        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public int deleteRMCBeanByRoomId(int roomId) {
        int resultCount;
        try {
            resultCount = db.delete("t_button", "RoomId=?",
                    new String[]{String.valueOf(roomId)});

        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public int insertMode(Mode mode) {
        int num = 0;
        try {
            ContentValues contentValues = new ContentValues();
            if (mode.getId() != 0) {
                contentValues.put("Id", mode.getId());
            }
            contentValues.put("ModeName", mode.getModeName());
            contentValues.put("ModeImage", mode.getModeImage());
            contentValues.put("ModeType", mode.getModeType());
            contentValues.put("ModeCode", mode.getModeCode());
            contentValues.put("Seqencing", mode.getSeqencing());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("StartTime", mode.getStartTime());
            contentValues.put("EndTime", mode.getEndTime());
            contentValues.put("ModeLoop", mode.getModeLoop());
            contentValues.put("ModeNickName", mode.getModeNickName());
            contentValues.put("GatewayID", mode.getGatewayID());
            contentValues.put("IsShow", mode.getIsShow());

            contentValues.put("floorName", mode.getFloorName());
            contentValues.put("floorId", mode.getFloorId());
            contentValues.put("roomId", mode.getRoomId());
            contentValues.put("roomName", mode.getRoomName());
            num += db.insert(TABLE_MODE, null, contentValues);
            // }
        } catch (Exception e) {
            System.out.println(e.toString());
            num = -1;
        }
        return num;
    }

    public int updateMode(Mode mode, String modeName) {
        int num = 0;
        ContentValues contentValues = new ContentValues();
        contentValues.put("ModeName", mode.getModeName());
        contentValues.put("ModeImage", mode.getModeImage());
        contentValues.put("ModeType", mode.getModeType());
        contentValues.put("ModeCode", mode.getModeCode());
        contentValues.put("Seqencing", mode.getSeqencing());
        contentValues.put("CreationTime", getSysNowTime());
        contentValues.put("StartTime", mode.getStartTime());
        contentValues.put("EndTime", mode.getEndTime());
        contentValues.put("ModeLoop", mode.getModeLoop());
        contentValues.put("ModeNickName", mode.getModeNickName());
        contentValues.put("GatewayID", mode.getGatewayID());
        contentValues.put("IsShow", mode.getIsShow());
        contentValues.put("floorName", mode.getFloorName());
        contentValues.put("floorId", mode.getFloorId());
        contentValues.put("roomId", mode.getRoomId());
        contentValues.put("roomName", mode.getRoomName());
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_MODE, new String[]{"Id"},
                        "ModeName=?", new String[]{mode.getModeName()}, null,
                        null, null, null);

                if (cursor.getCount() > 1) {
                    num = 2;
                }
                if (cursor.getCount() == 1) {
                    cursor.moveToLast();
                    int Id = cursor.getInt(0);

                    if (Id == mode.getId()) {
                        num = db.update(TABLE_MODE, contentValues, "Id=?",
                                new String[]{String.valueOf(mode.getId())});
                    } else {
                        num = 2;
                    }
                } else {
                    num = db.update(TABLE_MODE, contentValues, "Id=?",
                            new String[]{String.valueOf(mode.getId())});
                }
                cursor.close();
            } catch (Exception e) {
                System.out.println(e.toString());
                num = -1;
            }
        }
        return num;
    }

    public int updateDevice(Device device) {
        int num;
        ContentValues contentValues = new ContentValues();
        contentValues.put("DeviceName", device.getDeviceName());
        contentValues.put("DeviceImage", device.getDeviceImage());
        contentValues.put("DeviceID", device.getDeviceID());
        contentValues.put("DeviceNickName", device.getDeviceNickName());
        contentValues.put("CreationTime", getSysNowTime());
        contentValues.put("Seqencing", device.getSeqencing());
        contentValues.put("DeviceOnLine", device.getDeviceOnLine());
        contentValues.put("DeviceTimer", device.getDeviceTimer());
        contentValues.put("DeviceIO", device.getDeviceIO());
        contentValues.put("DeviceOrdered", device.getDeviceOrdered());
        contentValues.put("StartTime", device.getStartTime());
        contentValues.put("EndTime", device.getEndTime());
        contentValues.put("SceneId", device.getSceneId());
        contentValues.put("DeviceTypeKey", device.getDeviceTypeKey());
        contentValues.put("ProductsKey", device.getProductsKey());
        contentValues.put("GradingId", device.getGradingId());
        contentValues.put("Data1", device.getData1());
        contentValues.put("RoomId", device.getRoomId());
        contentValues.put("floorID", device.getFloorId());
        contentValues.put("floorName", device.getFloorName());
        contentValues.put("allOpen", device.getAllOpen());
        contentValues.put("allClose", device.getAllClose());
        try {
            num = db.update(TABLE_DEVICE, contentValues, "Id=?",
                    new String[]{String.valueOf(device.getId())});
        } catch (Exception e) {
            System.out.println(e.toString());
            num = -1;
        }
        return num;
    }

    public int updateRedFra(RedInfra infrared) {

        int num;
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put("InfraredName", infrared.getInfraredName());
            contentValues.put("InfraredImage", infrared.getInfraredImage());
            contentValues.put("DeviceId", infrared.getDeviceId());
            contentValues.put("FatherId", infrared.getFatherId());
            contentValues.put("InfraredIndex", infrared.getInfraredIndex());
            contentValues.put("InfraredBrandId", infrared.getInfraredBrandId());
            contentValues.put("InfraredBrandName", infrared.getInfraredBrandName());
            contentValues.put("InfraredVersionId", infrared.getInfraredVersionId());
            contentValues.put("InfraredVersionName", infrared.getInfraredVersionName());
            contentValues.put("InfraredKey", infrared.getInfraredKey());
            contentValues.put("InfraredButtonId", infrared.getInfraredButtonId());
            contentValues.put("InfraredCode", infrared.getInfraredCode());
            contentValues.put("InfraredStudyType", infrared.getInfraredStudyType());
            contentValues.put("InfraredX", infrared.getInfraredX());
            contentValues.put("InfraredY", infrared.getInfraredY());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("Seqencing", infrared.getSeqencing());
            num = db.update(TABLE_INFRARED, contentValues, "Id=?",
                    new String[]{String.valueOf(infrared.getId())});
        } catch (Exception e) {
            num = 0;
        }
        return num;
    }

    public int insertGateWay(GateWay gateWay) {
        int num = 0;
        if (db != null) {
            try {
                Cursor result = db.query(TABLE_GATEWAY,
                        new String[]{"GatewayName"}, "GatewayName=?",
                        new String[]{gateWay.getGatewayName()}, null, null,
                        null, null);
                if (result.getCount() > 0) {
                    num = -1;
                } else {
                    ContentValues contentValues = new ContentValues();
                    if (gateWay.getId() != 0) {
                        contentValues.put("Id", gateWay.getId());
                    }
                    contentValues.put("GatewayName", gateWay.getGatewayName());
                    contentValues.put("GatewayID", gateWay.getGatewayID());
                    contentValues.put("UserName", gateWay.getUserName());
                    contentValues.put("UserPassWord", gateWay.getUserPassWord());
                    contentValues.put("TypeId", gateWay.getTypeId());
                    contentValues.put("CreationTime", getSysNowTime());
                    contentValues.put("IsCurrent", gateWay.getIsCurrent());
                    contentValues.put("State", gateWay.getState());
                    contentValues.put("Seqencing", gateWay.getSeqencing());
                    contentValues.put("data1", gateWay.getData1());
                    contentValues.put("data2", gateWay.getData2());
                    num += db.insert(TABLE_GATEWAY, null, contentValues);
                }
                result.close();
            } catch (Exception e) {
                System.out.println(e.toString());
                num = 0;
            }
        }
        return num;
    }

    public int insertModeList(ModeList modeList) {
        int num;
        try {
            ContentValues contentValues = new ContentValues();
            if (modeList.getId() != 0) {
                contentValues.put("Id", modeList.getId());
            }

            contentValues.put("ModeAction", modeList.getModeAction());
            contentValues.put("ModeFunction", modeList.getModeFunction());
            contentValues.put("ModeTime", modeList.getModeTime());
            contentValues.put("ModeDelayed", modeList.getModeDelayed());
            contentValues.put("ModePeriod", modeList.getModePeriod());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("DeviceId", modeList.getDeviceId());
            contentValues.put("Seqencing", modeList.getSeqencing());
            contentValues.put("ModeId", modeList.getModeId());
            contentValues.put("BeginMonth", modeList.getBeginMonth());
            contentValues.put("EndMonth", modeList.getEndMonth());
            contentValues.put("LanguageId", 1);
            contentValues.put("CompanyId", 1);
            contentValues.put("Data1", "0");
            contentValues.put("Data2", "0");
            contentValues.put("ModeWeek", modeList.getModeWeek());
            contentValues.put("ModeDay", modeList.getModeDay());

            num = (int) db.insert(TABLE_MODE_LIST, null, contentValues);
        } catch (Exception e) {
            num = -1;
        }
        return num;
    }

    public int updateModeList(ModeList modeList) {
        int num;
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put("ModeAction", modeList.getModeAction());
            contentValues.put("ModeFunction", modeList.getModeFunction());
            contentValues.put("ModeTime", modeList.getModeTime());
            contentValues.put("ModeDelayed", modeList.getModeDelayed());
            contentValues.put("ModePeriod", modeList.getModePeriod());
            contentValues.put("CreationTime", modeList.getCreationTime());
            contentValues.put("DeviceId", modeList.getDeviceId());
            contentValues.put("Seqencing", modeList.getSeqencing());
            contentValues.put("ModeId", modeList.getModeId());
            contentValues.put("BeginMonth", modeList.getBeginMonth());
            contentValues.put("EndMonth", modeList.getEndMonth());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("LanguageId", 1);
            contentValues.put("CompanyId", 1);
            contentValues.put("Data1", "0");
            contentValues.put("Data2", "0");
            contentValues.put("ModeWeek", modeList.getModeWeek());
            contentValues.put("ModeDay", modeList.getModeDay());
            num = db.update(TABLE_MODE_LIST, contentValues, "Id=?",
                    new String[]{String.valueOf(modeList.getId())});
        } catch (Exception e) {
            num = -1;
        }
        return num;
    }

    public void updateDeviceType(DeviceType deviceType) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("DeviceTypeName", deviceType.getDeviceTypeName());
        contentValues.put("DeviceTypeKey", deviceType.getDeviceTypeKey());
        contentValues.put("DeviceTypeClick", deviceType.getDeviceTypeClick());
        contentValues.put("Seqencing", deviceType.getSeqencing());
        contentValues.put("IsShow", deviceType.getIsShow());
        contentValues.put("DeviceTypeImage", deviceType.getDeviceTypeImage());
        Cursor result = db.query(TABLE_DEVICE_TYPE,
                new String[]{"DeviceTypeName"}, "Id=?",
                new String[]{String.valueOf(deviceType.getId())}, null, null,
                null, null);
        if (result.getCount() > 0) {
            db.update(TABLE_DEVICE_TYPE, contentValues, "Id=?",
                    new String[]{String.valueOf(deviceType.getId())});
        } else {
            contentValues.put("Id", deviceType.getId());
            db.insert(TABLE_DEVICE_TYPE, null, contentValues);
        }
        result.close();
    }

    public void updateProducts(Products product) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("ProductsName", product.getProductsName());
        contentValues.put("ProductsCode", product.getProductsCode());
        contentValues.put("ProductsIO", product.getProductsIO());
        contentValues.put("ProductsKey", product.getProductsKey());
        contentValues.put("ActionShow", product.getActionShow());
        contentValues.put("FunctionShow", product.getFunctionShow());
        contentValues.put("ActionPramShow", product.getActionPramShow());
        contentValues.put("FunctionParamShow", product.getFunctionParamShow());
        contentValues.put("IsSwitch", product.getIsSwitch());

        Cursor result = db.query(TABLE_PRODUCTS,
                new String[]{"ProductsName"}, "Id=?",
                new String[]{String.valueOf(product.getId())}, null, null,
                null, null);
        if (result.getCount() > 0) {
            db.update(TABLE_PRODUCTS, contentValues, "Id=?",
                    new String[]{String.valueOf(product.getId())});
        } else {
            contentValues.put("Id", product.getId());
            db.insert(TABLE_PRODUCTS, null, contentValues);
        }
        result.close();
    }

    public int insertDevice(Device device) {
        int num = 0;
        try {
            ContentValues contentValues = new ContentValues();
            if (device.getId() != 0) {
                contentValues.put("Id", device.getId());
            }
            contentValues.put("DeviceName", device.getDeviceName());
            contentValues.put("DeviceImage", device.getDeviceImage());
            contentValues.put("DeviceID", device.getDeviceID());
            contentValues.put("DeviceNickName", device.getDeviceNickName());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("Seqencing", device.getSeqencing());
            contentValues.put("DeviceOnLine", device.getDeviceOnLine());
            contentValues.put("DeviceTimer", device.getDeviceTimer());
            contentValues.put("DeviceIO", device.getDeviceIO() != null ? device.getDeviceIO() : "0");
            contentValues.put("DeviceOrdered", device.getDeviceOrdered());
            contentValues.put("StartTime", device.getStartTime());
            contentValues.put("EndTime", device.getEndTime());
            contentValues.put("SceneId", device.getSceneId());
            contentValues.put("DeviceTypeKey", device.getDeviceTypeKey());
            contentValues.put("ProductsKey", device.getProductsKey());
            contentValues.put("GradingId", device.getGradingId());
            contentValues.put("RoomId", device.getRoomId());
            contentValues.put("CompanyId", device.getCompanyId());
            contentValues.put("LanguageId", device.getLanguageId());
            contentValues.put("RoleId", device.getRoleId());
            contentValues.put("Data1", device.getData1());
            contentValues.put("Data2", device.getData2());
            //contentValues.put("floorID", device.getFloorId());
            contentValues.put("floorID", 5);
            //contentValues.put("floorName", device.getFloorName());
            contentValues.put("floorName", "一楼");
            contentValues.put("allOpen", device.getAllOpen());
            contentValues.put("allClose", device.getAllClose());
            // YYX: 2018/11/22 修改后测试是否会不能添加
            num += db.insert(TABLE_DEVICE, null, contentValues);
            // YYX: 2018/11/30 如果再出现就放开close

//			db.close();
        } catch (Exception e) {
            // YYX: 2018/11/12 测试插入数据库失败？
            LogUtils.d("yyx设备插入数据库失败,失败信息:", e.toString());
            CrashReport.postCatchedException(e);
            num = -1;
        }
        return num;
    }

    public int updateGateWay(GateWay gateWay, String gateWayName) {
        int num = 0;
        ContentValues contentValues = new ContentValues();
        contentValues.put("GatewayName", gateWay.getGatewayName());
        contentValues.put("GatewayID", gateWay.getGatewayID());
        contentValues.put("UserName", gateWay.getUserName());
        contentValues.put("UserPassWord", gateWay.getUserPassWord());
        contentValues.put("TypeId", gateWay.getTypeId());
        contentValues.put("CreationTime", getSysNowTime());
        contentValues.put("IsCurrent", gateWay.getIsCurrent());
        contentValues.put("State", gateWay.getGateWayPoint());
        contentValues.put("data1", gateWay.getData1());
        contentValues.put("data2", gateWay.getData2());
        if (db != null) {
            try {
                Cursor cursor = db.query(TABLE_GATEWAY, new String[]{"Id"},
                        "GatewayName=?", new String[]{gateWay.getGatewayName()},
                        null, null, null, null);

                if (cursor.getCount() > 1) {
                    num = 2;
                }
                if (cursor.getCount() == 1) {
                    cursor.moveToLast();
                    int Id = cursor.getInt(0);

                    if (Id == gateWay.getId()) {
                        num = db.update(TABLE_GATEWAY, contentValues, "Id=?",
                                new String[]{String.valueOf(gateWay.getId())});
                    } else {
                        num = 2;
                    }

                } else {
                    num = db.update(TABLE_GATEWAY, contentValues, "Id=?",
                            new String[]{String.valueOf(gateWay.getId())});
                }
                cursor.close();
            } catch (Exception e) {
                System.out.println(e.toString());
                num = -1;
            }
        }
        return num;
    }


    public int updateIsCurrent(int Id, int current) {
        int resultCount;
        try {

            db.execSQL("update t_gateway set IsCurrent=0 where IsCurrent=1");
            db.execSQL("update t_gateway set IsCurrent=? where Id=?",
                    new Object[]{current, Id});
            resultCount = 1;
        } catch (Exception e) {
            resultCount = 0;
        }
        return resultCount;
    }

    @Override
    public int deleteGateWay(int Id) {
        int resultCount;
        try {
            resultCount = db.delete(TABLE_GATEWAY, "Id=?",
                    new String[]{String.valueOf(Id)});

        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public List<Room> getRoom() {
        List<Room> list = new ArrayList<Room>();

        if (db != null) {
            Cursor cursor = db.query(TABLE_ROOM, new String[]{"Id", "RoomName",
                            "RoomImage", "IsShow", "CreationTime", "Seqencing", "Data1", "floorId", "floorName"},
                    null, null, null, null, "Seqencing asc");
            int resultCount = cursor.getCount();
            try {
                if (resultCount == 0 || !cursor.moveToFirst()) {
                    return list;
                }
                for (int i = 0; i < resultCount; i++) {
                    Room room = new Room();
                    room.setId(cursor.getInt(0));
                    room.setRoomName(cursor.getString(1));
                    room.setRoomImage(cursor.getString(2));
                    room.setIsShow(cursor.getInt(3));
                    room.setCreationTime(cursor.getString(4));
                    room.setSeqencing(cursor.getInt(5));
                    room.setData1(cursor.getString(6));
                    room.setFloorId(cursor.getInt(7));
                    room.setFloorName(cursor.getString(8));
                    list.add(room);
                    cursor.moveToNext();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                cursor.close();
            }
        }
        return list;
    }

    public int insertRoom(Room room) {
        int num = 0;
        try {
            Cursor result = db.query(TABLE_ROOM, new String[]{"RoomName", "floorId"},
                    "RoomName=? and floorId=?", new String[]{room.getRoomName(),
                            String.valueOf(room.getFloorId())}, null,
                    null, null, null);

            if (result.getCount() > 0) {
                num = 1;
            } else {
                ContentValues contentValues = new ContentValues();
                if (room.getId() != 0) {
                    contentValues.put("Id", room.getId());
                }
                contentValues.put("RoomName", room.getRoomName());
                contentValues.put("RoomImage", room.getRoomImage());
                contentValues.put("CreationTime", getSysNowTime());
                contentValues.put("IsShow",
                        room.getIsShow());
                contentValues.put("Seqencing", room.getSeqencing());
                contentValues.put("Data1", room.getData1());
                contentValues.put("floorId", room.getFloorId());
                contentValues.put("floorName", room.getFloorName());
                contentValues.put("Data2", room.getData2());
                contentValues.put("avtar", room.getAvtar());
                num += db.insert(TABLE_ROOM, null, contentValues);
            }
            if (result != null) {
                result.close();
            }
        } catch (Exception e) {
            System.out.println(e.toString());
            num = -1;
        }
        return num;
    }

    /**
     * @param floor
     * @return private String floorName;
     * private String floorImage;
     * private String floorId;
     * private String CreationTime;
     * private int LanguageId;
     * private int Seqencing;
     * private int isShow;
     * private String actar;
     * private String graffitIndex;
     */

    public int insertFloor(Floor floor) {
        int num = 0;
        try {
            Cursor result = db.query(TABLE_FLOOR, new String[]{"floorName"},
                    "RoomName=?", new String[]{floor.getFloorName()}, null,
                    null, null, null);

            if (result.getCount() > 0) {
                num = 1;
            } else {
                ContentValues contentValues = new ContentValues();
                if (floor.getId() != 0) {
                    contentValues.put("Id", floor.getId());
                }
                contentValues.put("floorName", floor.getFloorName());
                contentValues.put("floorImage", floor.getFloorImage());
                contentValues.put("floorId", floor.getFloorId());
                contentValues.put("CreationTime", floor.getCreationTime());
                contentValues.put("LanguageId", floor.getLanguageId());
                contentValues.put("Seqencing", floor.getSeqencing());
                contentValues.put("isShow", floor.getIsShow());
                contentValues.put("actar", floor.getAvtar());
                contentValues.put("graffitIndex", floor.getGraffitIndex());
                num += db.insert(TABLE_FLOOR, null, contentValues);
            }
            if (result != null) {
                result.close();
            }
        } catch (Exception e) {
            System.out.println(e.toString());
            num = -1;
        }
        return num;
    }

    public int updateRoom(Room room, String RoomName) {
        int num = 0;
        ContentValues contentValues = new ContentValues();
        contentValues.put("RoomName", room.getRoomName());
        contentValues.put("RoomImage", room.getRoomImage());
        contentValues.put("CreationTime", getSysNowTime());
        contentValues.put("IsShow", room.getIsShow());
        contentValues.put("Seqencing", room.getSeqencing());
        contentValues.put("Data1", room.getData1());
        contentValues.put("floorId", room.getFloorId());
        contentValues.put("floorName", room.getFloorName());

        if (db != null) {
            try {
                Cursor result = db.query(TABLE_ROOM, new String[]{"RoomName", "floorId"},
                        "RoomName=? and floorId=?", new String[]{room.getRoomName(), String.valueOf(room.getFloorId())}, null,
                        null, null, null);
                if (result.getCount() > 0) {
                    num = 2;
                } else {
                    num = db.update(TABLE_ROOM, contentValues, "Id=?",
                            new String[]{String.valueOf(room.getId())});
                }
            } catch (Exception e) {
                System.out.println(e.toString());
                num = -1;
            }
        }
        return num;
//        try {
//            Cursor cursor = db.query(TABLE_ROOM, new String[]{"Id"},
//                    "RoomName=?", new String[]{room.getRoomName()}, null,
//                    null, null, null);
//
//            if (cursor.getCount() > 1) {
//                num = 2;
//            } else if (cursor.getCount() == 1) {
//                cursor.moveToLast();
//                int Id = cursor.getInt(cursor.getColumnIndex("Id"));
//
//
//                if (Id == room.getId()) {
//                    num = db.update(TABLE_ROOM, contentValues, "Id=?",
//                            new String[]{String.valueOf(room.getId())});
//                } else {
//                    num = 2;
//                }
//            } else {
//                num = db.update(TABLE_ROOM, contentValues, "Id=?",
//                        new String[]{String.valueOf(room.getId())});
//            }
//            if (!cursor.isClosed()) {
//                cursor.close();
//            }
//        } catch (Exception e) {
//            System.out.println(e.toString());
//            num = -1;
//        }
//        return num;
    }

    @Override
    public List<Device> getDevices() {
        List<Device> list = new ArrayList<Device>();
        try {
            String sql = "select D.*,D.Seqencing, a.RoomName, p.ProductsName,p.ProductsImage,p.ProductsCode," +
                    "p.IsSwitch,p.ProductsKey,p.ProductsIO,p.ActionShow,p.FunctionShow," +
                    "p.ActionPramShow,p.FunctionParamShow,DT.DeviceTypeKey,DT.IsShow," +
                    "DT.DeviceTypeClick,DT.DeviceTypeName FROM t_device D " +
                    "LEFT JOIN t_room a on d.RoomId=a.Id " +
                    "LEFT JOIN t_products p on d.ProductsKey=p.ProductsKey " +
                    "LEFT JOIN t_device_type dt on d.DeviceTypeKey=dt.DeviceTypeKey " +
                    "order by d.Seqencing asc";
            //Ling 2019.7.29 从数据库获取设备时由以前的按 “dt.Seqencing”和“d.Seqencing”排序改为只由 “d.Seqencing”排序
            //dt.Seqencing,
            if (db != null) {
                Cursor cursor = db.rawQuery(sql, null);
                if (cursor != null) {
                    int resultCount = cursor.getCount();
                    if (resultCount == 0 || !cursor.moveToFirst()) {
                        return list;
                    }
                    for (int i = 0; i < resultCount; i++) {
                        Device device = new Device();
                        device.setId(cursor.getInt(0));
                        device.setDeviceTypeClick(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeClick")));
                        device.setDeviceName(cursor.getString(cursor
                                .getColumnIndex("DeviceName")));
                        device.setDeviceTypeName(cursor.getString(cursor
                                .getColumnIndex("DeviceTypeName")));
                        device.setProductsName(cursor.getString(cursor
                                .getColumnIndex("ProductsName")));
                        device.setProductsCode(cursor.getString(cursor
                                .getColumnIndex("ProductsCode")));
                        device.setDeviceID(cursor.getString(cursor
                                .getColumnIndex("DeviceID")));
                        device.setIsShow(cursor.getInt(cursor
                                .getColumnIndex("IsShow")));
                        String deviceImage = cursor.getString(cursor
                                .getColumnIndex("DeviceImage"));
                        device.setDeviceImage(deviceImage);

//                        String deviceTypeImage = cursor.getString(cursor
//                                .getColumnIndex("DeviceTypeImage"));
//                        device.setDeviceTypeImage(deviceTypeImage);
//                        if (deviceImage == null || deviceImage == "" || deviceImage.equals("")) {
//                            deviceImage = cursor.getString(cursor
//                                    .getColumnIndex("ProductsImage"));
//                            com.blankj.utilcode.util.LogUtils.dTag("setDeviceImage==", "getDevices11 = " + deviceImage);
//                            device.setDeviceImage(deviceImage);
//                        } else {
//                            com.blankj.utilcode.util.LogUtils.dTag("setDeviceImage==", "getDevices12 = " + deviceImage);
//                            device.setDeviceImage(deviceImage);
//                        }
//                        com.blankj.utilcode.util.LogUtils.dTag("setDeviceImage==", "getDevices1 = " + deviceImage);

                        device.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        device.setDeviceIO(cursor.getString(cursor
                                .getColumnIndex("DeviceIO")));
                        device.setDeviceNickName(cursor.getString(cursor
                                .getColumnIndex("DeviceNickName")));
                        device.setSeqencing(cursor.getInt(cursor
                                .getColumnIndex("Seqencing")));
                        device.setDeviceOnLine(cursor.getInt(cursor
                                .getColumnIndex("DeviceOnLine")));
                        device.setDeviceTimer(cursor.getString(cursor
                                .getColumnIndex("DeviceTimer")));
                        device.setDeviceOrdered(cursor.getString(cursor
                                .getColumnIndex("DeviceOrdered")));
                        device.setStartTime(cursor.getString(cursor
                                .getColumnIndex("StartTime")));
                        device.setEndTime(cursor.getString(cursor
                                .getColumnIndex("EndTime")));
                        device.setSceneId(cursor.getInt(cursor
                                .getColumnIndex("SceneId")));
                        device.setRoomName(cursor.getString(cursor
                                .getColumnIndex("RoomName")));
                        device.setCreationTime(cursor.getString(cursor
                                .getColumnIndex("CreationTime")));
                        device.setGradingId(cursor.getInt(cursor
                                .getColumnIndex("GradingId")));
                        device.setRoomId(cursor.getInt(cursor
                                .getColumnIndex("RoomId")));
                        device.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        device.setIsSwitch(cursor.getInt(cursor
                                .getColumnIndex("IsSwitch")));
                        device.setProductsKey(cursor.getInt(cursor
                                .getColumnIndex("ProductsKey")));
                        device.setProductsIO(cursor.getInt(cursor
                                .getColumnIndex("ProductsIO")));
                        device.setActionShow(cursor.getInt(cursor
                                .getColumnIndex("ActionShow")));
                        device.setFunctionShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionShow")));
                        device.setActionPramShow(cursor.getInt(cursor
                                .getColumnIndex("ActionPramShow")));
                        device.setFunctionParamShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionParamShow")));
                        device.setData1(cursor.getString(cursor
                                .getColumnIndex("Data1")));
                        device.setData2(cursor.getString(cursor
                                .getColumnIndex("Data2")));
                        device.setFloorName(cursor.getString(cursor
                                .getColumnIndex("floorName")));
                        device.setFloorId(cursor.getInt(cursor
                                .getColumnIndex("floorID")));
                        device.setAllOpen(cursor.getInt(cursor
                                .getColumnIndex("allOpen")));
                        device.setAllClose(cursor.getInt(cursor
                                .getColumnIndex("allClose")));

                        device.setIsBackCode(0);
                        list.add(device);
                        cursor.moveToNext();
                    }
                    cursor.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Device> getSafeDevice() {
        List<Device> list = new ArrayList<>();

        String sql = "select D.*, a.RoomName, p.ProductsName,p.ProductsImage,p.ProductsCode,a.floorId,a.floorName," +
                "p.IsSwitch,p.ProductsKey,p.ProductsIO,p.ActionShow,p.FunctionShow," +
                "p.ActionPramShow,p.FunctionParamShow,DT.DeviceTypeKey,DT.IsShow," +
                "DT.DeviceTypeClick,DT.DeviceTypeName FROM t_device D " +
                "LEFT JOIN t_room a on d.RoomId=a.Id " +
                "LEFT JOIN t_products p on d.ProductsKey=p.ProductsKey " +
                "LEFT JOIN t_device_type dt on d.DeviceTypeKey=dt.DeviceTypeKey " +
                "WHERE D.DeviceTypeKey=18 " +
                "order by dt.Seqencing,d.Seqencing asc";
        try {
            if (db != null) {
                Cursor cursor = db.rawQuery(sql, null);
                if (cursor != null) {
                    int resultCount = cursor.getCount();
                    if (resultCount == 0 || !cursor.moveToFirst()) {
                        return list;
                    }
                    for (int i = 0; i < resultCount; i++) {
                        Device device = new Device();
                        device.setId(cursor.getInt(0));
                        device.setDeviceTypeClick(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeClick")));
                        device.setDeviceName(cursor.getString(cursor
                                .getColumnIndex("DeviceName")));
                        device.setDeviceTypeName(cursor.getString(cursor
                                .getColumnIndex("DeviceTypeName")));
                        device.setProductsName(cursor.getString(cursor
                                .getColumnIndex("ProductsName")));
                        device.setProductsCode(cursor.getString(cursor
                                .getColumnIndex("ProductsCode")));
                        device.setDeviceID(cursor.getString(cursor
                                .getColumnIndex("DeviceID")));
                        device.setIsShow(cursor.getInt(cursor
                                .getColumnIndex("IsShow")));
                        String deviceImage = cursor.getString(cursor
                                .getColumnIndex("DeviceImage"));
                        device.setDeviceImage(deviceImage);
                        device.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        device.setDeviceIO(cursor.getString(cursor
                                .getColumnIndex("DeviceIO")));
                        device.setDeviceNickName(cursor.getString(cursor
                                .getColumnIndex("DeviceNickName")));
                        device.setSeqencing(cursor.getInt(cursor
                                .getColumnIndex("Seqencing")));
                        device.setDeviceOnLine(cursor.getInt(cursor
                                .getColumnIndex("DeviceOnLine")));
                        device.setDeviceTimer(cursor.getString(cursor
                                .getColumnIndex("DeviceTimer")));
                        device.setDeviceOrdered(cursor.getString(cursor
                                .getColumnIndex("DeviceOrdered")));
                        device.setStartTime(cursor.getString(cursor
                                .getColumnIndex("StartTime")));
                        device.setEndTime(cursor.getString(cursor
                                .getColumnIndex("EndTime")));
                        device.setSceneId(cursor.getInt(cursor
                                .getColumnIndex("SceneId")));
                        device.setRoomName(cursor.getString(cursor
                                .getColumnIndex("RoomName")));
                        device.setCreationTime(cursor.getString(cursor
                                .getColumnIndex("CreationTime")));
                        device.setGradingId(cursor.getInt(cursor
                                .getColumnIndex("GradingId")));
                        device.setRoomId(cursor.getInt(cursor
                                .getColumnIndex("RoomId")));
                        device.setDeviceTypeKey(cursor.getInt(cursor
                                .getColumnIndex("DeviceTypeKey")));
                        device.setIsSwitch(cursor.getInt(cursor
                                .getColumnIndex("IsSwitch")));
                        device.setProductsKey(cursor.getInt(cursor
                                .getColumnIndex("ProductsKey")));
                        device.setProductsIO(cursor.getInt(cursor
                                .getColumnIndex("ProductsIO")));
                        device.setActionShow(cursor.getInt(cursor
                                .getColumnIndex("ActionShow")));
                        device.setFunctionShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionShow")));
                        device.setActionPramShow(cursor.getInt(cursor
                                .getColumnIndex("ActionPramShow")));
                        device.setFunctionParamShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionParamShow")));
                        device.setData1(cursor.getString(cursor
                                .getColumnIndex("Data1")));
                        device.setFloorId(cursor.getInt(cursor
                                .getColumnIndex("floorId")));
                        device.setFloorName(cursor.getString(cursor
                                .getColumnIndex("floorName")));

                        device.setIsBackCode(0);
                        list.add(device);
                        cursor.moveToNext();
                    }
                    if (!cursor.isClosed()) {
                        cursor.close();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Device> getModeDevice(int modeById) {
        List<Device> list = new ArrayList<Device>();
        try {
            String sql = "select ml.*,d.DeviceID,d.DeviceIO,d.StartTime,d.EndTime,d.DeviceName,p.ProductsName,p.ProductsCode,p.ActionShow,p.FunctionShow,p.ActionPramShow,p.FunctionParamShow, DT.DeviceTypeName FROM t_mode_list ml left join t_device d on ml.DeviceId=d.Id left join t_products p on d.deviceProductId=p.Id LEFT JOIN t_device_type dt on d.DeviceTypeKey=dt.Id left join t_device_type DT on d.DeviceTypeKey=DT.Id where ml.ModeId="
                    + modeById + " order by dt.Seqencing asc";
            if (db != null) {
                Cursor cursor = db.rawQuery(sql, null);
                if (cursor != null) {
                    int resultCount = cursor.getCount();

                    if (resultCount == 0 || !cursor.moveToFirst()) {
                        return list;
                    }
                    for (int i = 0; i < resultCount; i++) {
                        Device device = new Device();
                        device.setDeviceName(cursor.getString(cursor
                                .getColumnIndex("DeviceName")));
                        device.setDeviceTypeName(cursor.getString(cursor
                                .getColumnIndex("DeviceTypeName")));
                        device.setProductsName(cursor.getString(cursor
                                .getColumnIndex("ProductsName")));
                        device.setProductsCode(cursor.getString(cursor
                                .getColumnIndex("ProductsCode")));
                        device.setDeviceID(cursor.getString(cursor
                                .getColumnIndex("DeviceID")));
                        device.setDeviceIO(cursor.getString(cursor
                                .getColumnIndex("DeviceIO")));
                        device.setStartTime(cursor.getString(cursor
                                .getColumnIndex("StartTime")));
                        device.setEndTime(cursor.getString(cursor
                                .getColumnIndex("EndTime")));
                        device.setGradingId(cursor.getInt(cursor
                                .getColumnIndex("GradingId")));
                        device.setActionShow(cursor.getInt(cursor
                                .getColumnIndex("ActionShow")));
                        device.setFunctionShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionShow")));
                        device.setActionPramShow(cursor.getInt(cursor
                                .getColumnIndex("ActionPramShow")));
                        device.setFunctionParamShow(cursor.getInt(cursor
                                .getColumnIndex("FunctionParamShow")));
                        list.add(device);
                        cursor.moveToNext();
                    }
                }
                if (!cursor.isClosed()) {
                    cursor.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public void updateDeviceSort(List<Device> list) {
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_device set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});
        }
    }

    @Override
    public void updateGatewaySort(List<GateWay> list) {
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_gateway set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});
        }
    }

    @Override
    public void updateModeSort(List<Mode> list) {
        // TODO: 2019/4/10 可以直接交换位置
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_mode set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});

        }
    }

    @Override
    public void updateRoomSort(List<Room> list) {
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_room set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});


        }
    }

    public void updateRedInfraSort(List<RedInfra> list) {

        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_infrared set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});
        }
    }

    public void updateModeListSort(List<ModeList> list) {
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_mode_list set Seqencing=? where Id=?",
                    new Object[]{i, list.get(i).getId()});

        }
    }

    @Override
    public void updateRoomHide(int homePageDisplays, int Id) {
        db.execSQL("update t_room set homePageDisplays=? where Id=?",
                new Object[]{homePageDisplays, Id});


    }

    @Override
    public List<DeviceType> getDeviceTypes() {
        List<DeviceType> list = new ArrayList<DeviceType>();
        try {
            Cursor cursor = db.query(TABLE_DEVICE_TYPE, new String[]{"Id",
                    "DeviceTypeName", "DeviceTypeImage", "DeviceTypeKey", "DeviceTypeClick", "IsShow",
                    "Seqencing"}, null, null, null, null, "Seqencing asc");
            int resultCount = cursor.getCount();
            if (resultCount == 0 || !cursor.moveToFirst()) {
                return list;
            }
            for (int i = 0; i < resultCount; i++) {
                DeviceType deviceType = new DeviceType();
                deviceType.setId(cursor.getInt(0));
                deviceType.setDeviceTypeName(cursor.getString(cursor
                        .getColumnIndex("DeviceTypeName")));
                deviceType.setDeviceTypeImage(cursor.getString(cursor
                        .getColumnIndex("DeviceTypeImage")));
                deviceType.setSeqencing(cursor.getInt(cursor
                        .getColumnIndex("Seqencing")));
                deviceType.setDeviceTypeKey(cursor.getInt(cursor
                        .getColumnIndex("DeviceTypeKey")));
                deviceType.setDeviceTypeClick(cursor.getInt(cursor
                        .getColumnIndex("DeviceTypeClick")));
                deviceType.setIsShow(cursor.getInt(cursor
                        .getColumnIndex("IsShow")));
                list.add(deviceType);
                cursor.moveToNext();
            }
            if (!cursor.isClosed()) {
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public void updateDeviceTypeSort(List<DeviceType> list) {
        for (int i = 0; i < list.size(); i++) {
            db.execSQL("update t_device_type set Seqencing=? where Id=?",
                    new Object[]{(i + 1), list.get(i).getId()});
        }
    }

    @Override
    public int deleteRoom(int Id) {
        int resultCount = 0;
        try {
            resultCount += db.delete(TABLE_DEVICE, "RoomId=?",
                    new String[]{String.valueOf(Id)});
            resultCount += db.delete(TABLE_ROOM, "Id=?",
                    new String[]{String.valueOf(Id)});

        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public int deleteRedFra(int Id) {
        int resultCount;
        try {
            resultCount = db.delete(TABLE_INFRARED, "Id=?",
                    new String[]{String.valueOf(Id)});
        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    @Override
    public int deleteDevice(int Id) {
        int resultCount = 0;
        try {
            resultCount += db.delete(TABLE_MODE_LIST, "DeviceId=?",
                    new String[]{String.valueOf(Id)});
            resultCount += db.delete(TABLE_INFRARED, "FatherId=?",
                    new String[]{String.valueOf(Id)});
            resultCount += db.delete(TABLE_DEVICE, "Id=?",
                    new String[]{String.valueOf(Id)});
        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public String getSysNowTime() {
        Date now = new Date();
        java.text.DateFormat format = new java.text.SimpleDateFormat(
                "yyyy-MM-dd");
        String formatTime = format.format(now);
        return formatTime;
    }

    @Override
    public int deleteMode(int Id) {
        int resultCount = 0;
        try {
            resultCount += db.delete(TABLE_MODE_LIST, "ModeId=?",
                    new String[]{String.valueOf(Id)});

            resultCount += db.delete(TABLE_MODE, "Id=?",
                    new String[]{String.valueOf(Id)});

        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public int deleteModelist(int Id) {
        int resultCount;
        try {
            resultCount = db.delete(TABLE_MODE_LIST, "Id=?",
                    new String[]{String.valueOf(Id)});
        } catch (Exception e) {
            resultCount = -1;
        }
        return resultCount;
    }

    public int updateFraID(String DeviceID, int infra_index) {
        try {
            String sql = "update t_infrared set infrared_ID = '" + DeviceID
                    + "'" + " where FatherId=?" + infra_index;
            db.execSQL(sql);
        } catch (Exception e) {
            return -1;
        }
        return 0;
    }

    public void execSQL(String sql) {
        db.execSQL(sql);
    }

    @Override
    public List<RedInfra> getInfrareds() {
        List<RedInfra> list = new ArrayList<RedInfra>();
        try {
            String sql = "select *,l.Id as lid,l.Seqencing as lseqencing from t_infrared l left join t_device d on d.Id=l.FatherId where l.FatherId=d.Id order by l.Seqencing";
            Cursor cursor = db.rawQuery(sql, null);
            int resultCount = cursor.getCount();
            if (resultCount == 0 || !cursor.moveToFirst()) {
                return list;
            }
            for (int i = 0; i < resultCount; i++) {
                RedInfra infrared = new RedInfra();
                infrared.setId(cursor.getInt(cursor.getColumnIndex("lid")));
                infrared.setInfraredName(cursor.getString(cursor
                        .getColumnIndex("InfraredName")));
                infrared.setInfraredImage(cursor.getString(cursor
                        .getColumnIndex("InfraredImage")));
                infrared.setDeviceId(cursor.getString(cursor
                        .getColumnIndex("DeviceId")));
                infrared.setFatherId(cursor.getInt(cursor
                        .getColumnIndex("FatherId")));
                infrared.setInfraredIndex(cursor.getInt(cursor
                        .getColumnIndex("InfraredIndex")));
                infrared.setInfraredBrandId(cursor.getInt(cursor
                        .getColumnIndex("InfraredBrandId")));
                infrared.setInfraredBrandName(cursor.getString(cursor
                        .getColumnIndex("InfraredBrandName")));
                infrared.setInfraredVersionId(cursor.getInt(cursor
                        .getColumnIndex("InfraredVersionId")));
                infrared.setInfraredVersionName(cursor.getString(cursor
                        .getColumnIndex("InfraredVersionName")));
                infrared.setInfraredKey(cursor.getInt(cursor
                        .getColumnIndex("InfraredKey")));
                infrared.setInfraredButtonId(cursor.getInt(cursor
                        .getColumnIndex("InfraredButtonId")));
                infrared.setInfraredCode(cursor.getString(cursor
                        .getColumnIndex("InfraredCode")));
                infrared.setInfraredStudyType(cursor.getInt(cursor
                        .getColumnIndex("InfraredStudyType")));
                infrared.setInfraredX(cursor.getInt(cursor
                        .getColumnIndex("InfraredX")));
                infrared.setInfraredY(cursor.getInt(cursor
                        .getColumnIndex("InfraredY")));
                infrared.setCreationTime(cursor.getString(cursor
                        .getColumnIndex("CreationTime")));
                infrared.setSeqencing(cursor.getInt(cursor
                        .getColumnIndex("lseqencing")));
                list.add(infrared);
                cursor.moveToNext();
            }
            if (!cursor.isClosed()) {
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int insertVoice(VoiceInfo voiceInfo) {
        int num;
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put("voiceName", voiceInfo.getVoiceName());
            contentValues.put("voiceAnswer", voiceInfo.getVoiceAnswer());
            contentValues.put("voiceAction", voiceInfo.getVoiceAction());
            contentValues.put("userName", voiceInfo.getUserName());
            db.insert(TABLE_VOICE, null, contentValues);
            num = 1;
        } catch (Exception e) {
            num = -1;
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int insertInfrad(RedInfra infrared) {
        int num = 0;
        try {
            ContentValues contentValues = new ContentValues();
            if (infrared.getId() != 0) {
                contentValues.put("Id", infrared.getId());
            }
            contentValues.put("InfraredName", infrared.getInfraredName());
            contentValues.put("InfraredImage", infrared.getInfraredImage());
            contentValues.put("DeviceId", infrared.getDeviceId());
            contentValues.put("FatherId", infrared.getFatherId());
            contentValues.put("InfraredIndex", infrared.getInfraredIndex());
            contentValues.put("InfraredBrandId", infrared.getInfraredBrandId());
            contentValues.put("InfraredBrandName", infrared.getInfraredBrandName());
            contentValues.put("InfraredVersionId",
                    infrared.getInfraredVersionId());
            contentValues.put("InfraredVersionName",
                    infrared.getInfraredVersionName());
            contentValues.put("InfraredKey",
                    infrared.getInfraredKey());
            contentValues.put("InfraredButtonId", infrared.getInfraredButtonId());
            contentValues.put("InfraredCode", infrared.getInfraredCode());
            contentValues.put("InfraredStudyType", infrared.getInfraredStudyType());
            contentValues.put("InfraredX", infrared.getInfraredX());
            contentValues.put("InfraredY", infrared.getInfraredY());
            contentValues.put("CreationTime", getSysNowTime());
            contentValues.put("Seqencing", infrared.getSeqencing());
            num += db.insert(TABLE_INFRARED, null, contentValues);
        } catch (Exception e) {
            num = -1;
        }
        return num;
    }

    /**
     * 检查某表列是否存在
     *
     * @param tableName  表名
     * @param columnName 列名
     * @return true 有此列(字段) false 无此列(字段)
     */
    @Override
    public boolean checkColumnExist(String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;
        try {
            //查询一行
            cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);
            result = cursor != null && cursor.getColumnIndex(columnName) != -1;
        } catch (Exception e) {
            LogUtils.d("checkColumnExist", e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return result;
    }

    @Override
    public int deleteInfrad(int Id) {
        return 0;
    }

    public int deleteSyncData(String table) {
        return db.delete(table, null, null);
    }


    //Ling 2019.8.8 新加更换设备房间id的方法，用于房间设备管理界面
    public void changeRoomID(int roomId, int id) {
        String sql = "UPDATE t_device SET RoomId=" + roomId + " WHERE Id=" + id;
        db.execSQL(sql);
    }

    @Override
    public String getProductsCodeByProductsKey(int productsKey) {
        String string = "00";
        Cursor c = db.rawQuery("select ProductsCode from t_products where ProductsKey =" + productsKey, null);
        if (c.moveToFirst()) {
            string = c.getString(0);
            c.close();
            return string;
        }
        c.close();
        return string;
    }

    public void updateGatewayName(String gatewayId, String name) {
        db.execSQL("update t_gateway set GatewayName=? where GatewayID=?",
                new Object[]{name, gatewayId});
    }

    public int updateFloor(Floor floor) {
        int num;
        ContentValues contentValues = new ContentValues();
        contentValues.put("floorName", floor.getFloorName());
        contentValues.put("floorImage", floor.getFloorImage());
        contentValues.put("floorId", floor.getFloorId());
        contentValues.put("creationTime", floor.getCreationTime());
        contentValues.put("languageId", floor.getLanguageId());
        contentValues.put("Seqencing", floor.getSeqencing());
        contentValues.put("isShow", floor.getIsShow());
        contentValues.put("avtar", floor.getAvtar());
        contentValues.put("graffitIndex", floor.getGraffitIndex());
        contentValues.put("nikeName", floor.getNikeName());
        try {
            num = db.update(TABLE_FLOOR, contentValues, "Id=?",
                    new String[]{String.valueOf(floor.getId())});
        } catch (Exception e) {
            System.out.println(e.toString());
            num = -1;
        }
        return num;
    }
}
